import pymysql


class MysqlManager(object):
    """
    mysql 数据库连接管理器
    pip install pymysql==1.1.1
    """

    def __init__(self, host, port, database, user, passwd):
        self.host = host
        self.port = port
        self.database = database
        self.user = user
        self.password = passwd
        self.client = None

    def connect(self):
        self.client = pymysql.connect(host=self.host, port=self.port, database=self.database, user=self.user,
                                      passwd=self.password, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

    def close(self):
        if self.client:
            self.client.close()

    def select(self, table, columns="*"):
        if self.client:
            cursor = self.client.cursor(pymysql.cursors.DictCursor)
            sql = f"""select {columns} from {table}"""
            cursor.execute(sql)  # 执行sql语句
            row = cursor.fetchmany(3)
            for item in row:
                print(item)
            cursor.close()

    def update(self, table, update_info=None, condition=None):
        try:
            with self.client.cursor() as cursor:
                # 定义更新数据的 SQL 语句
                sql = f"UPDATE {table} SET {update_info} WHERE {condition}"
                # 执行 SQL 语句，传入参数
                cursor.execute(sql)
            # 提交事务
            self.client.commit()
            print("数据更新成功！")

        except pymysql.err.MySQLError as e:
            # 如果发生错误，回滚事务
            self.client.rollback()
            print(f"数据更新失败: {e}")


if __name__ == '__main__':
    url = "https://lmt.jihai8.com"
    sql = MysqlManager("192.168.77.1", 3306, "score_referral", "score_dev", "score666")
    sql.connect()
    sql.update("score_grep", f"content='{url}'", "title='mlive'")
    sql.select("score_grep")
    sql.close()
